unit summary;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Rptbs, Menus, frxClass, frxPreview, ExtCtrls, Buttons, StdCtrls,
  ComCtrls, RzButton, CheckLst, frxExportPDF, frxExportXML, frxExportXLS,
  frxExportHTML, frxExportTXT, frxExportRTF, frxExportImage, frxBarcode,
  frxBDEComponents, frxCross, frxDMPExport, frxOLE, frxDCtrl, frxChart,
  frxRich, frxDesgn, frxGZip, frxDBSet, DB, StrUtils, ADODB;

type
  Tsummaryfrm = class(TRptbsfrm)
    Label4: TLabel;
    ckContractor: TCheckListBox;
    ckScaleID: TCheckListBox;
    Label5: TLabel;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    GroupBox1: TGroupBox;
    RadioButton1: TRadioButton;
    RadioButton2: TRadioButton;
    RadioButton3: TRadioButton;
    RadioButton4: TRadioButton;
    RadioButton5: TRadioButton;
    Label6: TLabel;
    CheckBox3: TCheckBox;
    ckProducts: TCheckListBox;
    rangeRfid: TListBox;
    procedure FormCreate(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure RzBitBtn2Click(Sender: TObject);
    procedure RzBitBtn1Click(Sender: TObject);
    procedure palMenuResize(Sender: TObject);
    procedure RzBitBtn3Click(Sender: TObject);
    procedure CheckBox3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure comboRFIDChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure FrmExit;override;
    procedure refreshDB;
    function getRptName:string;
  end;

var
  summaryfrm: Tsummaryfrm;

implementation

uses dbmodule, includefile;

{$R *.dfm}

var
  sclist:String;
  //activepicker:string;
  
function Tsummaryfrm.getRptName:string;
var
  tmp:string;
begin
  if RadioButton1.Checked then begin
    SMRRpt:=0;
    if showGW then
      tmp:=GetAppPath+'\fr3\summary_picker_pro.fr3'
    else
      tmp:=GetAppPath+'\fr3\summary_picker_pro_no.fr3';
  end;
  if RadioButton2.Checked then begin
    SMRRpt:=1;
    if showGW then
      tmp:=GetAppPath+'\fr3\summary_picker.fr3'
    else
      tmp:=GetAppPath+'\fr3\summary_picker_no.fr3';
  end;
  if RadioButton3.Checked then begin
    SMRRpt:=2;
    if showGW then
      tmp:=GetAppPath+'\fr3\summary_con_pro.fr3'
    else
      tmp:=GetAppPath+'\fr3\summary_con_pro_no.fr3';
  end;
  if RadioButton4.Checked then begin
    SMRRpt:=3;
    if showGW then
      tmp:=GetAppPath+'\fr3\summary_con.fr3'
    else
      tmp:=GetAppPath+'\fr3\summary_con_no.fr3';
  end;
  Result:=tmp;
end;

procedure Tsummaryfrm.refreshDB;
var
  starttime,endtime,STM,ETM:TDateTime;
  rfid:string;
  contractorlist:String;
  scalelist:String;
  rfidlist:string;
  productlist:string;
  i:integer;
begin
  starttime:=strdtpicker.DateTime;
  endtime:=enddtpicker.DateTime;
  rfid:=Trim(LeftStr(comboRFID.Text,10));
  contractorlist:='';
  for i:=0 to ckContractor.Items.Count-1 do begin
    if ckContractor.Checked[i] then begin
      if contractorlist='' then
        contractorlist:=LeftStr(ckContractor.Items.Strings[i],3)
      else
        contractorlist:=contractorlist+','+LeftStr(ckContractor.Items.Strings[i],3);
    end;
  end;

  productlist:='';
  for i:= 0 to ckProducts.Items.Count-1 do begin
    if ckProducts.Checked[i] then
      if productList='' then begin
        productlist:=''''+ckProducts.Items.Strings[i]+'''';
      end else begin
        productlist:=productlist+','+''''+ckProducts.Items.Strings[i]+'''';
      end;
  end;

  scalelist:='';
  sclist:='';
  for i:=0 to ckScaleID.Items.Count-1 do begin
    if ckScaleID.Checked[i] then
      if scalelist='' then begin
        scalelist:=''''+ckScaleID.Items.Strings[i]+'''';
        sclist:=ckScaleID.Items.Strings[i];
      end else begin
        scalelist:=scalelist+','+''''+ckScaleID.Items.Strings[i]+'''';
        sclist:=sclist+','+ckScaleID.Items.Strings[i];
      end;
  end;
  with dbRpt do begin
    Active:=False;
    SQL.Clear;
    SQL.Add('SELECT DATETIME FROM RECORD');
    SQL.Add(FORMAT('WHERE DATETIME<#%s# And DATETIME>=#%s# ',[FormatDateTime('YYYY-MM-DD',endtime+1),
                        FormatDateTime('YYYY-MM-DD',starttime)]));
    SQL.Add('ORDER BY HOUR(DATETIME)');
    //showmessage(sql.Text);
    execsql;
    Active:=True;
    First;
    STM:=fieldbyname('DATETIME').AsDateTime;
    Last;
    ETM:=fieldbyname('DATETIME').AsDateTime;
    Active:=False;
    SQL.Clear;
    SQL.Add(Format('Select PRODUCT.PROALIAS as PRODUCTID,Right(RECORD.RFID,%d) as RFID,',[RFIDLen]));
    SQL.add({'RECORD.SCALEID,}'EMPLOYEE.NAME,SUM(RECORD.TRAYS) as TRAYS,');
    SQL.Add('CONTRACTOR.NAME as CNAME,');
    SQL.Add(Format('CDATE(''%s'') As Starttime, CDate(''%s'') As Endtime,',[FormatDateTime('YYYY-MM-DD',starttime),
                        FormatDateTime('YYYY-MM-DD',endtime)]));
    SQL.Add(Format('''%s'' As STM, ''%s'' As ETM,',[FormatDateTime('hh:mm:ss',STM),FormatDateTime('hh:mm:ss',ETM)]));
    SQL.Add('SUM(RECORD.NW) as NW,SUM(RECORD.GW) as GW from PRODUCT,RECORD,EMPLOYEE,CONTRACTOR');
    SQL.Add('Where RECORD.RFID=EMPLOYEE.RFID');
    SQL.Add('And CONTRACTOR.ID=EMPLOYEE.CONTRACTORNAME');
    SQL.Add('And PRODUCT.PRONAME=RECORD.PRODUCTID');
    SQL.Add(FORMAT('And DATETIME<#%s# And DATETIME>=#%s#',[FormatDateTime('YYYY-MM-DD',endtime+1),
                        FormatDateTime('YYYY-MM-DD',starttime)]));
    if contractorlist<>'' then
      SQL.Add(Format('And CONTRACTOR.ID IN (%s)',[contractorlist]));
    if scalelist<>'' then                                                
      SQL.Add(Format('And SCALEID IN (%s)',[scalelist]));
    if productlist<>'' then
      SQL.Add(Format('And PRONAME IN (%s)',[ProductList]));
    if rfid<>'All' then begin
      if rfid<>'Range' then
        SQL.Add(Format('And Right(RECORD.RFID,%d)=''%s''',[RFIDLen,rfid]))
      else begin
        rfid:='';
        for i:=0 to RangeRfid.Items.Count-1 do begin
          if RangeRfid.Selected[i] then begin
            if rfid='' then
              rfid:=''''+RangeRfid.Items.Strings[i]+''''
            else
              rfid:=rfid+','+''''+RangeRfid.Items.Strings[i]+'''';
          end;
        end;
        if rfid<>'' then
          SQL.Add(Format('And Right(RECORD.RFID,%d) in(%s)',[RFIDLen,rfid]));
      end;
    end;
    if not alwRFID100 then
      SQL.Add(Format('And CLng(Right(RECORD.RFID,%d))>100',[RFIDLen]));
    SQL.Add('Group by PRODUCT.PROALIAS,RECORD.RFID,EMPLOYEE.NAME,CONTRACTOR.NAME');
    SQL.Add(format('Order by Right(RECORD.RFID,%d),CONTRACTOR.NAME,PRODUCT.PROALIAS',[RFIDLen]));
    //showmessage(sql.Text);
    execsql;
    Active:=True;
  end;
  RptDs.UserName:='RECORD';

end;

procedure Tsummaryfrm.FrmExit;
begin
  hide;
end; 

procedure Tsummaryfrm.FormCreate(Sender: TObject);
var
  I:integer;
begin
  inherited;
  for I:=0 to ComponentCount-1 do begin
    if Components[I] is TLabel then begin
      if 0<>TLabel(Components[I]).Tag then
        TLabel(Components[I]).Caption:=
                GetCaption(name,TLabel(Components[I]).Tag);
    end;
    if Components[I] is TRzBitBtn then begin
      if 0<>TRzBitBtn(Components[I]).Tag then
        TRzBitBtn(Components[I]).Caption:=
                GetCaption(name,TRzBitBtn(Components[I]).Tag);
    end;
  end;
  dbmodulefrm.getcontractorlist(ckContractor.Items);
  dbmodulefrm.getScaleID(ckScaleID.Items);
  dbmodulefrm.getProducts(ckProducts.Items);
  case SMRRpt of
    0:RadioButton1.Checked:=True;
    1:RadioButton2.Checked:=True;
    2:RadioButton3.Checked:=True;
    3:RadioButton4.Checked:=True;
  end;
  CheckBox1.Checked:=True;
  CheckBox2.Checked:=True;
  CheckBox3.Checked:=True;
end;

procedure Tsummaryfrm.CheckBox1Click(Sender: TObject);
var
  I:integer;
begin
  inherited;
  For i:=0 to ckContractor.Items.Count-1 do begin
    ckContractor.Checked[i]:=CheckBox1.Checked;
  end;
end;

procedure Tsummaryfrm.CheckBox2Click(Sender: TObject);
var
  I:integer;
begin
  inherited;
  For i:=0 to ckScaleID.Items.Count-1 do begin
    ckScaleID.Checked[i]:=CheckBox2.Checked;
  end;
end;

procedure Tsummaryfrm.RzBitBtn2Click(Sender: TObject);
begin
  inherited;
  refreshDB;
  frxReport.LoadFromFile(getRptName);
  frxReport.DesignReport;
end;

procedure Tsummaryfrm.RzBitBtn1Click(Sender: TObject);
var
  yy:TfrxMemoView;
begin
  inherited;
  refreshDB;
  frxReport.LoadFromFile(getRptName);
  yy:=frxReport.findObject('scaleList') as TfrxMemoView;
  if nil<>yy then
    yy.Text:=sclist;
  frxReport.ShowReport;
end;

procedure Tsummaryfrm.palMenuResize(Sender: TObject);
begin
  RzBitBtn1.Left:=palMenu.Width-RzBitBtn1.Width-2;
  RzBitBtn2.Left:=palMenu.Width-RzBitBtn2.Width-2;
  RzBitBtn3.Left:=palMenu.Width-RzBitBtn3.Width-2;
end;

procedure Tsummaryfrm.RzBitBtn3Click(Sender: TObject);
begin
  inherited;
  frxReport.Print;
  refreshDB;
  frxReport.ShowReport;
end;

procedure Tsummaryfrm.CheckBox3Click(Sender: TObject);
var
  i:integer;
begin
  inherited;
  For i:=0 to ckProducts.Items.Count-1 do begin
    ckProducts.Checked[i]:=CheckBox2.Checked;
  end;
end;

procedure Tsummaryfrm.FormShow(Sender: TObject);
begin
  inherited;
  comboRFID.Items.Insert(1,'Range');
end;

procedure Tsummaryfrm.comboRFIDChange(Sender: TObject);
begin
  inherited;
  if (1=comboRfid.ItemIndex) then begin
    sptbtn.Enabled:=False;
    rangeRfid.Enabled:=true;
    RangeRfid.Items:=comboRfid.Items;
    RangeRfid.Items.Delete(1);
    RangeRfid.Items.Delete(0);
  end else
    rangeRfid.Enabled:=False;
end;

end.
